package commentControl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import util.mysqlUtil;


public class CommentDao {
	String tableName;

	public CommentDao(String tableName) {
		this.tableName = tableName;
	}
	
	public Comment select(Connection conn, int commentnumber) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from "
					+ tableName + " where comment_number = ?");
			pstmt.setInt(1, commentnumber);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return makeCommentFromResultSet(rs);
			} else {
				return null;
			}
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}
	
	protected Comment makeCommentFromResultSet(ResultSet rs)
			throws SQLException {
		// number id title start finish time content date
		Comment comment = new Comment();
		comment.setNumber(rs.getInt("number"));
		comment.setId(rs.getString("id"));
		comment.setComment_number(rs.getInt("comment_number"));
		comment.setComment(rs.getString("comment"));

		return comment;
	}
	//////////////////////////////////////////
		
	public int selectCount(Connection conn,int number) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			//rs = stmt.executeQuery("select count(*) from 'carpool'.'"
				//	+ tableName + "'");
			rs = stmt.executeQuery("select count(*) from "+tableName+" where number ='"+number+"'");
			rs.next();
			return rs.getInt(1);
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(stmt);
		}
	}

	public int delete(Connection conn, int comment_number) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("delete from " + tableName
					+ "' where comment_number = ?");
			pstmt.setInt(1, comment_number);
			return pstmt.executeUpdate();
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}

	public int insert(Connection conn, Comment comment) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn
					.prepareStatement("INSERT INTO "
							+ tableName
							+ " (number , comment ,id) "
							+ "VALUES (?,?,?);");

			pstmt.setInt(1, comment.getNumber());
			pstmt.setString(2, comment.getComment());
			pstmt.setString(3, comment.getId());
			return pstmt.executeUpdate();
		} finally {
			mysqlUtil.close(pstmt);
		}
	}

	public List<Comment> selectList(Connection conn, int number)
			throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn
					.prepareStatement("select * from(select * from "+tableName+" where number = ?)a order by a.comment_number asc");

			pstmt.setInt(1, number);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				List<Comment> commentList = new ArrayList<Comment>();
				do {
					commentList.add(makeCommentFromResultSet(rs));
				} while (rs.next());
				return commentList;
			} else {
				return Collections.emptyList();
			}
		} finally {
			mysqlUtil.close(rs);
			mysqlUtil.close(pstmt);
		}
	}
}
